package com.zhuoshi.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.zhuoshi.bean.ContractSubBean;
import com.zhuoshi.utils.DbHelper;

/**
 * 销售合同子表
 * 
 * @author Administrator
 *
 */
public class ContractSubDao {

	// 添加
	public int add(ContractSubBean bean) {
		Connection conn = null;
		PreparedStatement pstat = null;
		int rows = 0;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append(
					"insert into contractsub (numerical,sqarename,modeltype,unit,price,totalcount,totalmoney) values");
			sql.append(" (?,?,?,?,?,?,?)");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setInt(1, bean.getNumerical());
			pstat.setString(2, bean.getSqarename());
			pstat.setString(3, bean.getModeltype());
			pstat.setString(4, bean.getUnit());
			pstat.setDouble(5, bean.getPrice());
			pstat.setInt(6, bean.getTotalcount());
			pstat.setDouble(7, bean.getTotalmoney());
			
			rows = pstat.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.dispose(conn, pstat, null);
		}

		return rows;
	}

	// 修改
	public int update(ContractSubBean bean) {
		Connection conn = null;
		PreparedStatement pstat = null;
		int rows = 0;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("update contractsub set ");
			sql.append("numerical=?,");
			sql.append("sqarename=?,");
			sql.append("modeltype=?,");
			sql.append("unit=?,");
			sql.append("price=?,");
			sql.append("totalcount=?,");
			sql.append("totalmoney=?");
			sql.append(" where id=?");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setInt(1, bean.getNumerical());
			pstat.setString(2, bean.getSqarename());
			pstat.setString(3, bean.getModeltype());
			pstat.setString(4, bean.getUnit());
			pstat.setDouble(5, bean.getPrice());
			pstat.setInt(6, bean.getTotalcount());
			pstat.setDouble(7, bean.getTotalmoney());
			pstat.setInt(8, bean.getId());
			rows = pstat.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.dispose(conn, pstat, null);
		}

		return rows;
	}

	// 删除
	public int delete(int id) {
		Connection conn = null;
		PreparedStatement pstat = null;
		int rows = 0;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("delete from contractsub where id=?");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setInt(1, id);
			rows = pstat.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.dispose(conn, pstat, null);
		}

		return rows;
	}

	/**
	 * 通过id查询 获取一个数据实体
	 * 
	 * @param id
	 *            自增ID
	 * @return
	 */
	public ContractSubBean getModel(int id) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		ContractSubBean bean = null;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("select * from contractsub where id=?");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			while (rs.next()) {
				bean = new ContractSubBean();
				bean.setId(rs.getInt("id"));
				bean.setNumerical(rs.getInt("numerical"));
				bean.setSqarename(rs.getString("sqarename"));
				bean.setModeltype(rs.getString("modeltype"));
				bean.setUnit(rs.getString("unit"));
				bean.setPrice(rs.getDouble("price"));
				bean.setTotalcount(rs.getInt("totalcount"));
				bean.setTotalmoney(rs.getDouble("totalmoney"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.dispose(conn, pstat, rs);
		}
		return bean;
	}

	/**
	 * 查询多条 List
	 * 
	 * @param bean
	 *            条件 如果没有条件 传入null 查询所有
	 * @return
	 */
	public List<ContractSubBean> getList(ContractSubBean bean, String startDate, String endDate) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<ContractSubBean> list = new ArrayList<ContractSubBean>();
		try {
			conn = DbHelper.getConnection();
			// ---===================构建查询条件=================---//
			StringBuffer sql = new StringBuffer();
			sql.append("select * from contractsub where 1=1");
			if (bean != null) {

				if (null != bean.getNumerical()) {// 合同数字编号
					sql.append(" and numerical like '%" + bean.getNumerical() + "%'");
				}
				if (!"".equals(startDate) && startDate != null) {
					sql.append(" and condate>='" + startDate + "'");
				}

				if (!"".equals(endDate) && endDate != null) {
					sql.append(" and condate<='" + endDate + "'");
				}

			}
			// ---===================/构建查询条件=================---//
			pstat = conn.prepareStatement(sql.toString());
			// System.out.println(sql);
			rs = pstat.executeQuery();
			while (rs.next()) {
				ContractSubBean cBean = new ContractSubBean();
				cBean.setId(rs.getInt("id"));
				cBean.setNumerical(rs.getInt("numerical"));
				cBean.setSqarename(rs.getString("sqarename"));
				cBean.setModeltype(rs.getString("modeltype"));
				cBean.setUnit(rs.getString("unit"));
				cBean.setPrice(rs.getDouble("price"));
				cBean.setTotalcount(rs.getInt("totalcount"));
				cBean.setTotalmoney(rs.getDouble("totalmoney"));
				list.add(cBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.dispose(conn, pstat, rs);
		}
		return list;
	}

	public List<ContractSubBean> getAllList() {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<ContractSubBean> list = new ArrayList<ContractSubBean>();
		ContractSubBean cBean = null;
		try {
			conn = DbHelper.getConnection();
			// ---===================构建查询条件=================---//
			StringBuffer sql = new StringBuffer();
			sql.append("select * from contractsub where 1=1");
			// ---===================/构建查询条件=================---//
			pstat = conn.prepareStatement(sql.toString());
			// System.out.println(sql);
			rs = pstat.executeQuery();
			while (rs.next()) {
				cBean = new ContractSubBean();
				cBean.setId(rs.getInt("id"));
				cBean.setNumerical(rs.getInt("numerical"));
				cBean.setSqarename(rs.getString("sqarename"));
				cBean.setModeltype(rs.getString("modeltype"));
				cBean.setUnit(rs.getString("unit"));
				cBean.setPrice(rs.getDouble("price"));
				cBean.setTotalcount(rs.getInt("totalcount"));
				cBean.setTotalmoney(rs.getDouble("totalmoney"));
				list.add(cBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.dispose(conn, pstat, rs);
		}
		return list;
	}
	
	public double getTotalMoney(int numerical){
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		double result = 0d;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("select sum(totalmoney) from contractsub where 1=1");
			sql.append(" and numerical=?");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setInt(1, numerical);
			rs = pstat.executeQuery();
			while(rs.next()){
				result = rs.getDouble(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DbHelper.dispose(conn, pstat, rs);
		}
		return result;
	}
	
	
	//通过合同数字编号查询信息
	public List<ContractSubBean> getList(ContractSubBean bean){
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<ContractSubBean> list = new ArrayList<ContractSubBean>();
		try {
			conn = DbHelper.getConnection();
			// ---===================构建查询条件=================---//
			StringBuffer sql = new StringBuffer();
			sql.append("select * from contractsub where 1=1");
			if (bean != null) {

				if (null != bean.getNumerical()) {// 合同数字编号
					sql.append(" and numerical='" + bean.getNumerical() + "'");
				}

			}
			// ---===================/构建查询条件=================---//
			pstat = conn.prepareStatement(sql.toString());
			// System.out.println(sql);
			rs = pstat.executeQuery();
			while (rs.next()) {
				ContractSubBean cBean = new ContractSubBean();
				cBean.setId(rs.getInt("id"));
				cBean.setNumerical(rs.getInt("numerical"));
				cBean.setSqarename(rs.getString("sqarename"));
				cBean.setModeltype(rs.getString("modeltype"));
				cBean.setUnit(rs.getString("unit"));
				cBean.setPrice(rs.getDouble("price"));
				cBean.setTotalcount(rs.getInt("totalcount"));
				cBean.setTotalmoney(rs.getDouble("totalmoney"));
				list.add(cBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.dispose(conn, pstat, rs);
		}
		return list;
	}
	
	
	public ContractSubBean getModel(String sqarename,int numerical) {
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		ContractSubBean bean = null;
		try {
			conn = DbHelper.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append("select * from contractsub where sqarename=? and numerical=?");
			pstat = conn.prepareStatement(sql.toString());
			pstat.setString(1, sqarename);
			pstat.setInt(2, numerical);
			rs = pstat.executeQuery();
			while (rs.next()) {
				bean = new ContractSubBean();
				bean.setId(rs.getInt("id"));
				bean.setNumerical(rs.getInt("numerical"));
				bean.setSqarename(rs.getString("sqarename"));
				bean.setModeltype(rs.getString("modeltype"));
				bean.setUnit(rs.getString("unit"));
				bean.setPrice(rs.getDouble("price"));
				bean.setTotalcount(rs.getInt("totalcount"));
				bean.setTotalmoney(rs.getDouble("totalmoney"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbHelper.dispose(conn, pstat, rs);
		}
		return bean;
	}
	
	
}
